from flask import Blueprint, request, jsonify
import json
import logging
from collections import OrderedDict
from utils.utils import build_fail_response, confirm_params_type
from database.db_tools import SQLServerDatabase, connection_string
from datetime import datetime, timedelta
# 创建蓝图
ts_recheck_res_page = Blueprint('ts_recheck_res_page', __name__)



# 精确查找唯一值
@ts_recheck_res_page.route('/pyapi/getTsRecheckResByOrderNumberAndSnAndIsfinal', methods=['GET'])
def getTsRecheckResByOrderNumberAndSnAndIsfinal():
    try:
        orderNumber = request.args.get('orderNumber')
        binSerialNumber = request.args.get('binSerialNumber')
        recheckStartTime = request.args.get('recheckStartTime')
        recheckEndTime = request.args.get('recheckEndTime')

        # todo
        # 统一将结束时间调大一秒，避免查询数据为空，暂时不知道怎么解决这个bug
        # 将字符串转换为 datetime 对象
        endTime_dt = datetime.strptime(recheckEndTime, "%Y-%m-%d %H:%M:%S")
        # 将结束时间增加一秒
        endTime_dt += timedelta(seconds=1)
        # 将调整后的结束时间转换为字符串
        recheckEndTime = endTime_dt.strftime("%Y-%m-%d %H:%M:%S")
        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_query_all("ts_recheck_res",
                                   filters=f"orderNumber = ? AND binSerialNumber = ? AND  recheckTime BETWEEN ? AND ? AND isFinal = 1",
                                   params=[orderNumber, binSerialNumber, recheckStartTime, recheckEndTime])
            return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))


# 获取当前传入数据库的最新的复检日期
@ts_recheck_res_page.route('/pyapi/getRecheckDateToShow', methods=['GET'])
def getRecheckDateToShow():
    try:
        orderNumber = request.args.get('orderNumber')
        binSerialNumber = request.args.get('binSerialNumber')


        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_query_all("ts_recheck_res",
                                   filters=f"orderNumber = ? AND binSerialNumber = ?  AND isFinal = 1 ORDER BY recheckTime DESC",
                                   params=[orderNumber, binSerialNumber],
                                   customSelectColumns= 'TOP 1 recheckTime')
            return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))



# 复检日志页面查询
@ts_recheck_res_page.route('/pyapi/getRecheckResByConditions', methods=['POST'])
def getRecheckResByConditions():
    try:
        orderNumber =  request.json.get('orderNumber')
        binSerialNumber = request.json.get('binSerialNumber')
        checkRes = request.json.get('checkRes')
        pageindex = request.json.get('pageindex')
        pagesize = request.json.get('pagesize')
        # 动态拼接参数，支持多条件查询
        filterArr = []
        if orderNumber != '':
            filterArr.append(f"orderNumber LIKE '%{orderNumber}%'")
        if checkRes != '':
            filterArr.append(f"checkRes = {checkRes}")
        if binSerialNumber != '':
            filterArr.append(f"binSerialNumber LIKE '%{binSerialNumber}%'")
        filters = " AND ".join(filterArr)
        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_query_pagination("ts_recheck_res",
                                   filters=filters,
                                   page=pageindex,
                                   page_size=pagesize)
        return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))

# 将复检信息存入复检总表
@ts_recheck_res_page.route('/pyapi/save_recheck_info_all', methods=['POST'])
def save_recheck_info_all():
    try:
        # 获取参数对象
        data = OrderedDict(sorted(request.json.items()))
        columns = []
        values = []
        # 封装key和value
        for key, value in data.items():
            columns.append(key)
            values.append(value)
        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_insert("ts_recheck_res", values=values, columns=columns)
            return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))


